﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using Microsoft.SqlServer.Management.Common;

namespace ECommerce.Web.SqlServer
{
    public class SqlServerScriptRunner
    {
        private readonly string connectionString;

        public SqlServerScriptRunner(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void RunScriptsInFolder(string scriptsFolderPath)
        {
            IEnumerable<FileInfo> files = GetScriptFilesInFolder(scriptsFolderPath);

            if (!files.Any())
            {
                return;
            }
            
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {             
                ServerConnection serverConnection = new ServerConnection(connection);

                serverConnection.BeginTransaction();

                try
                {
                    foreach (var file in files.OrderBy(x => x.Name))
                    {
                        string sql = File.ReadAllText(file.FullName);

                        serverConnection.ExecuteNonQuery(sql);
                    }

                    serverConnection.CommitTransaction();
                }
                catch (SqlException)
                {
                    serverConnection.RollBackTransaction();

                    throw;
                }
            }
        }

        IEnumerable<FileInfo> GetScriptFilesInFolder(string scriptsFolderPath)
        {
            DirectoryInfo directory = new DirectoryInfo(scriptsFolderPath);

            return directory.GetFiles("*.sql", SearchOption.AllDirectories);
        }
    }
}
